﻿-- Revo.Infrastructure SQL baseline schema for common providers (EF Core, EF6)
-- MSSQL version

-- EVENT STORE

CREATE TABLE [dbo].[RES_EVENT_STREAM] (
	[RES_EVS_EventStreamId] [UNIQUEIDENTIFIER] NOT NULL,
	[RES_EVS_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[RES_EVS_Version] [INT] NOT NULL,
	[RES_EVS_MetadataJson] [NVARCHAR] (MAX),
	CONSTRAINT [RES_EVENT_STREAM_PK] PRIMARY KEY NONCLUSTERED ([RES_EVS_EventStreamId])
);

CREATE TABLE [dbo].[RES_EVENT_STREAM_ROW] (
	[RES_ESR_EventStreamRowId] [UNIQUEIDENTIFIER] NOT NULL,
	[RES_ESR_GlobalSequenceNumber] [BIGINT] IDENTITY(1,1) NOT NULL,
	[RES_ESR_StreamId] [UNIQUEIDENTIFIER] NOT NULL,
	[RES_ESR_StreamSequenceNumber] [BIGINT] NOT NULL,
	[RES_ESR_StoreDate] [DATETIMEOFFSET] NOT NULL,
	[RES_ESR_EventName] [VARCHAR] (50) NOT NULL,
	[RES_ESR_EventVersion] [INT] NOT NULL,
	[RES_ESR_EventJson] [NVARCHAR] (MAX) NOT NULL,
	[RES_ESR_AdditionalMetadataJson] [NVARCHAR] (MAX),
	[RES_ESR_IsDispatchedToAsyncQueues] [BIT] NOT NULL
	CONSTRAINT [RES_EVENT_STREAM_ROW_PK] PRIMARY KEY NONCLUSTERED ([RES_ESR_EventStreamRowId]),
	CONSTRAINT [RES_EVENT_STREAM_ROW_FK_EVENT_STREAM] FOREIGN KEY ([RES_ESR_StreamId]) REFERENCES [dbo].[RES_EVENT_STREAM] ([RES_EVS_EventStreamId]),
	CONSTRAINT [RES_EVENT_STREAM_ROW_UK_EVENT_STREAM_ID_AND_STREAM_SEQUENCE_NUMBER] UNIQUE([RES_ESR_StreamId], [RES_ESR_StreamSequenceNumber])
);

CREATE CLUSTERED INDEX [CIX_RES_EVENT_STREAM] ON [dbo].[RES_EVENT_STREAM] ([RES_EVS_Ordinal]);
CREATE UNIQUE CLUSTERED INDEX [CIX_RES_EVENT_STREAM_ROW] ON [dbo].[RES_EVENT_STREAM_ROW] ([RES_ESR_GlobalSequenceNumber]);

-- ASYNC EVENTS

CREATE TABLE [dbo].[RAE_ASYNC_EVENT_QUEUE] (
	[RAE_AEQ_AsyncEventQueueId] [VARCHAR] (128) NOT NULL,
	[RAE_AEQ_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[RAE_AEQ_Version] [INT] NOT NULL,
	[RAE_AEQ_LastSequenceNumberProcessed] [BIGINT],
	CONSTRAINT [RAE_ASYNC_EVENT_QUEUE_PK] PRIMARY KEY NONCLUSTERED ([RAE_AEQ_AsyncEventQueueId])
);

CREATE TABLE [dbo].[RAE_EXTERNAL_EVENT_RECORD] (
	[RAE_EER_ExternalEventRecordId] [UNIQUEIDENTIFIER] NOT NULL,
	[RAE_EER_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[RAE_EER_Version] [INT] NOT NULL,
	[RAE_EER_EventName] [VARCHAR] (50) NOT NULL,
	[RAE_EER_EventVersion] [INT] NOT NULL,
	[RAE_EER_EventJson] [NVARCHAR] (MAX) NOT NULL,
	[RAE_EER_MetadataJson] [NVARCHAR] (MAX),
	[RAE_EER_IsDispatchedToAsyncQueues] bit
	CONSTRAINT [RAE_EXTERNAL_EVENT_RECORD_PK] PRIMARY KEY NONCLUSTERED ([RAE_EER_ExternalEventRecordId])
);

CREATE TABLE [dbo].[RAE_QUEUED_ASYNC_EVENT] (
	[RAE_QAE_QueuedAsyncEventId] [UNIQUEIDENTIFIER] NOT NULL,
	[RAE_QAE_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[RAE_QAE_QueueId] [VARCHAR] (128) NOT NULL,
	[RAE_QAE_SequenceNumber] [BIGINT],
	[RAE_QAE_EventStreamRowId] [UNIQUEIDENTIFIER],
	[RAE_QAE_ExternalEventRecordId] [UNIQUEIDENTIFIER],
	CONSTRAINT [RAE_QUEUED_ASYNC_EVENT_PK] PRIMARY KEY NONCLUSTERED ([RAE_QAE_QueuedAsyncEventId]),
	CONSTRAINT [RAE_QUEUE_ID_FK_ASYNC_EVENT_QUEUE] FOREIGN KEY ([RAE_QAE_QueueId]) REFERENCES [dbo].[RAE_ASYNC_EVENT_QUEUE] ([RAE_AEQ_AsyncEventQueueId]),
	CONSTRAINT [RAE_EVENT_STREAM_ROW_ID_FK_EVENT_STREAM_ROW] FOREIGN KEY ([RAE_QAE_EventStreamRowId]) REFERENCES [dbo].[RES_EVENT_STREAM_ROW] ([RES_ESR_EventStreamRowId]),
	CONSTRAINT [RAE_EXTERNAL_EVENT_RECORD_ID_FK_EXTERNAL_EVENT_RECORD] FOREIGN KEY ([RAE_QAE_ExternalEventRecordId]) REFERENCES [dbo].[RAE_EXTERNAL_EVENT_RECORD] ([RAE_EER_ExternalEventRecordId])
);

CREATE UNIQUE CLUSTERED INDEX [CIX_RAE_ASYNC_EVENT_QUEUE] ON [dbo].[RAE_ASYNC_EVENT_QUEUE] ([RAE_AEQ_Ordinal]);
CREATE UNIQUE CLUSTERED INDEX [CIX_RAE_EXTERNAL_EVENT_RECORD] ON [dbo].[RAE_EXTERNAL_EVENT_RECORD] ([RAE_EER_Ordinal]);
CREATE UNIQUE CLUSTERED INDEX [CIX_RAE_QUEUED_ASYNC_EVENT] ON [dbo].[RAE_QUEUED_ASYNC_EVENT] ([RAE_QAE_Ordinal]);
CREATE UNIQUE NONCLUSTERED INDEX [UIX_RAE_QUEUED_ASYNC_EVENT] ON [dbo].[RAE_QUEUED_ASYNC_EVENT] ([RAE_QAE_QueueId], [RAE_QAE_SequenceNumber]) WHERE [RAE_QAE_SequenceNumber] IS NOT NULL;

-- SAGAS

CREATE TABLE [dbo].[REV_SAGA_METADATA_RECORD] (
	[REV_SMR_SagaMetadataRecordId] [UNIQUEIDENTIFIER] NOT NULL,
	[REV_SMR_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[REV_SMR_ClassId] [UNIQUEIDENTIFIER] NOT NULL
	CONSTRAINT [REV_SAGA_METADATA_RECORD_PK] PRIMARY KEY NONCLUSTERED ([REV_SMR_SagaMetadataRecordId])
);

CREATE TABLE [dbo].[REV_SAGA_METADATA_KEY] (
	[REV_SMK_SagaMetadataKeyId] [UNIQUEIDENTIFIER] NOT NULL,
	[REV_SMK_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[REV_SMK_SagaId] [UNIQUEIDENTIFIER] NOT NULL,
	[REV_SMK_KeyName] [VARCHAR] (128) NOT NULL,
	[REV_SMK_KeyValue] [NVARCHAR] (MAX) NOT NULL
	CONSTRAINT [REV_SAGA_METADATA_KEY_PK] PRIMARY KEY NONCLUSTERED ([REV_SMK_SagaMetadataKeyId]),
	CONSTRAINT [REV_SAGA_METADATA_KEY_FK_SAGA_ID] FOREIGN KEY ([REV_SMK_SagaId]) REFERENCES [dbo].[REV_SAGA_METADATA_RECORD] ([REV_SMR_SagaMetadataRecordId])
);

CREATE CLUSTERED INDEX [CIX_REV_SAGA_METADATA_RECORD] ON [dbo].[REV_SAGA_METADATA_RECORD] ([REV_SMR_Ordinal]);
CREATE CLUSTERED INDEX [CIX_REV_SAGA_METADATA_KEY] ON [dbo].[REV_SAGA_METADATA_KEY] ([REV_SMK_Ordinal]);